﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_qc_rm
{
    public DAL_tbl_qc_rm()
    {

    }



    public List<tbl_qc_rm> select(int ID)
    {
        List<tbl_qc_rm> list = new List<tbl_qc_rm>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,RM_Order_Number,Bag_Number,Drum_Number,Date,Al,C,Ca,Fe,Grade,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_qc_rm where ID=@ID";
        cmd.Parameters.AddWithValue("@ID", ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_qc_rm()
            {
                ID = Convert.ToInt32(dr["ID"]),
                RM_Order_Number = dr["RM_Order_Number"].ToString(),
                Bag_Number = dr["Bag_Number"].ToString(),
                Drum_Number = dr["Drum_Number"].ToString(),
                Date = dr["Date"].toDateTimeWithoutError(),
                Al = Convert.ToInt32(dr["Al"]),
                C = Convert.ToDecimal(dr["C"]),
                Ca = Convert.ToInt32(dr["Ca"]),
                Fe = Convert.ToInt32(dr["Fe"]),
                Grade = dr["Grade"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public List<tbl_qc_rm> selectAll()
    {
        List<tbl_qc_rm> list = new List<tbl_qc_rm>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,RM_Order_Number,Bag_Number,Drum_Number,Date,Al,C,Ca,Fe,Grade,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_qc_rm Order By ID";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_qc_rm()
            {
                ID = Convert.ToInt32(dr["ID"]),
                RM_Order_Number = dr["RM_Order_Number"].ToString(),
                Bag_Number = dr["Bag_Number"].ToString(),
                Drum_Number = dr["Drum_Number"].ToString(),
                Date = dr["Date"].toDateTimeWithoutError(),
                Al = Convert.ToInt32(dr["Al"]),
                C = Convert.ToDecimal(dr["C"]),
                Ca = Convert.ToInt32(dr["Ca"]),
                Fe = Convert.ToInt32(dr["Fe"]),
                Grade = dr["Grade"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }

    public DataTable selectAllForGrid()
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = @"Select ID,RM_Order_Number,CASE WHEN Bag_Number='null' THEN '' WHEN Bag_Number='0' THEN '' ELSE Bag_Number END as 'Bag_Number',CASE WHEN Drum_Number='null' THEN '' WHEN Drum_Number='0' THEN '' ELSE Drum_Number END as 'Drum_Number',Date as Date,Al,C,Ca,Fe,Grade,CASE WHEN IS_ACTIVE =1 THEN 'True' ELSE 'False' END as 'IS_ACTIVE',CREATED_BY,CREATED_DATE as CREATED_DATE,UPDATED_BY,UPDATED_DATE as UPDATED_DATE from tbl_qc_rm Order By ID desc";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        return dt;
    }

    public bool insert(List<tbl_qc_rm> list)
    {
        bool ret = false;
        List<MySqlCommand> cmds = new List<MySqlCommand>();
        string sql = @"Insert into tbl_qc_rm(RM_Order_Number, Bag_Number, Drum_Number, Date, Al, C, Ca, Fe, Grade, CREATED_BY, CREATED_DATE)
                        values(@RM_Order_Number, @Bag_Number, @Drum_Number, @Date, @Al, @C, @Ca, @Fe, @Grade, @CREATED_BY, @CREATED_DATE)";

        foreach (tbl_qc_rm row in list)
        {
            MySqlCommand cmd = new MySqlCommand(sql);

            cmd.Parameters.AddWithValue("@RM_Order_Number", row.RM_Order_Number);
            cmd.Parameters.AddWithValue("@Bag_Number", row.Bag_Number);
            cmd.Parameters.AddWithValue("@Drum_Number", row.Drum_Number);
            cmd.Parameters.AddWithValue("@Date", row.Date);
            cmd.Parameters.AddWithValue("@Al", row.Al);
            cmd.Parameters.AddWithValue("@C", row.C);
            cmd.Parameters.AddWithValue("@Ca", row.Ca);
            cmd.Parameters.AddWithValue("@Fe", row.Fe);
            cmd.Parameters.AddWithValue("@Grade", row.Grade);
            cmd.Parameters.AddWithValue("@CREATED_BY", HttpContext.Current.User.Identity.Name);
            cmd.Parameters.AddWithValue("@CREATED_DATE", DateTime.Now);
            cmds.Add(cmd);
        }
        ret = DBUtil.ExecuteNonQuery(cmds);
        return ret;
    }


    public int insert(String RM_Order_Number, String Bag_Number, String Drum_Number, DateTime Date, int Al, Decimal C, int Ca, int Fe, String Grade, String CREATED_BY, DateTime CREATED_DATE)
    {
        int i = 0;
        string sql = @"Insert into tbl_qc_rm(RM_Order_Number, Bag_Number, Drum_Number, Date, Al, C, Ca, Fe, Grade, CREATED_BY, CREATED_DATE)
                        values(@RM_Order_Number, @Bag_Number, @Drum_Number, @Date, @Al, @C, @Ca, @Fe, @Grade, @CREATED_BY, @CREATED_DATE)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@RM_Order_Number", RM_Order_Number);
        cmd.Parameters.AddWithValue("@Bag_Number", Bag_Number);
        cmd.Parameters.AddWithValue("@Drum_Number", Drum_Number);
        cmd.Parameters.AddWithValue("@Date", Date);
        cmd.Parameters.AddWithValue("@Al", Al);
        cmd.Parameters.AddWithValue("@C", C);
        cmd.Parameters.AddWithValue("@Ca", Ca);
        cmd.Parameters.AddWithValue("@Fe", Fe);
        cmd.Parameters.AddWithValue("@Grade", Grade);
        cmd.Parameters.AddWithValue("@CREATED_BY", HttpContext.Current.User.Identity.Name);
        cmd.Parameters.AddWithValue("@CREATED_DATE", DateTime.Now);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }

    public bool update(List<tbl_qc_rm> list)
    {
        bool ret = false;
        List<MySqlCommand> cmds = new List<MySqlCommand>();
        string sql = @"Update tbl_qc_rm SET RM_Order_Number=@RM_Order_Number, Bag_Number=@Bag_Number, Drum_Number=@Drum_Number, Date=@Date, Al=@Al, C=@C, Ca=@Ca, Fe=@Fe, Grade=@Grade, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE ID=@ID";

        foreach (tbl_qc_rm row in list)
        {
            MySqlCommand cmd = new MySqlCommand(sql);

            cmd.Parameters.AddWithValue("@ID", row.ID);
            cmd.Parameters.AddWithValue("@RM_Order_Number", row.RM_Order_Number);
            cmd.Parameters.AddWithValue("@Bag_Number", row.Bag_Number);
            cmd.Parameters.AddWithValue("@Drum_Number", row.Drum_Number);
            cmd.Parameters.AddWithValue("@Date", row.Date);
            cmd.Parameters.AddWithValue("@Al", row.Al);
            cmd.Parameters.AddWithValue("@C", row.C);
            cmd.Parameters.AddWithValue("@Ca", row.Ca);
            cmd.Parameters.AddWithValue("@Fe", row.Fe);
            cmd.Parameters.AddWithValue("@Grade", row.Grade);
            cmd.Parameters.AddWithValue("@UPDATED_BY", HttpContext.Current.User.Identity.Name);
            cmd.Parameters.AddWithValue("@UPDATED_DATE", DateTime.Now);

            cmds.Add(cmd);
        }
        ret = DBUtil.ExecuteNonQuery(cmds);
        return ret;
    }



    public int update(int ID, String RM_Order_Number, String Bag_Number, String Drum_Number, DateTime Date, int Al, Decimal C, int Ca, int Fe, String Grade, String UPDATED_BY, DateTime UPDATED_DATE)
    {
        int i = 0;
        string sql = @"Update tbl_qc_rm SET RM_Order_Number=@RM_Order_Number, Bag_Number=@Bag_Number, Drum_Number=@Drum_Number, Date=@Date, Al=@Al, C=@C, Ca=@Ca, Fe=@Fe, Grade=@Grade, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);
        cmd.Parameters.AddWithValue("@RM_Order_Number", RM_Order_Number);
        cmd.Parameters.AddWithValue("@Bag_Number", Bag_Number);
        cmd.Parameters.AddWithValue("@Drum_Number", Drum_Number);
        cmd.Parameters.AddWithValue("@Date", Date);
        cmd.Parameters.AddWithValue("@Al", Al);
        cmd.Parameters.AddWithValue("@C", C);
        cmd.Parameters.AddWithValue("@Ca", Ca);
        cmd.Parameters.AddWithValue("@Fe", Fe);
        cmd.Parameters.AddWithValue("@Grade", Grade);
        cmd.Parameters.AddWithValue("@UPDATED_BY", HttpContext.Current.User.Identity.Name);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", DateTime.Now);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int delete(int ID)
    {
        int i = 0;
        string sql = "Delete FROM tbl_qc_rm WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



}